﻿CREATE PROCEDURE [dbo].[utl_AddHousekeeping]
@Table [sysname]=NULL, @Force BIT=0
AS
BEGIN
	DECLARE @True  BIT; SET @True  = 1
	DECLARE @False BIT; SET @False = 0

	DECLARE @CRLF VARCHAR(2); SET @CRLF = CHAR(13) + CHAR(10)

	DECLARE @SQL           NVARCHAR(MAX)
	DECLARE @ErrorCode     INT
	DECLARE @RowsAffected  INT
	DECLARE @Message       VARCHAR(MAX)
	DECLARE @FromClause    VARCHAR(MAX)
	DECLARE @ID            INT
	DECLARE @ColumnName    sysname
	DECLARE @TriggerName   sysname
	DECLARE @RC            INT
	DECLARE @RevMdlName	   sysname; SET @RevMdlName = OBJECT_NAME(@@PROCID)

	DECLARE @KeyColumns    TABLE (
	            ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
	            ColumnName sysname
	        )
	
	-- see if table name was passed
	IF @Table IS NULL
	BEGIN
	    SET @Message = 'Must pass @Table parameter.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 1
	END
	
	-- see if table exists
	IF dbo.fn_TableExists(@Table) = @False
	BEGIN
	    SET @Message = 'Table ''' + @Table + ''' not found.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 2
	END
	
	-- see if table has a primary key
	IF dbo.fn_TableHasPrimaryKey(@Table) = @False
	BEGIN
	    SET @Message = 'Table ''' + @Table + ''' does not have a primary key.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 3
	END
	
	IF @Force = @True
	BEGIN
	    EXECUTE @RC = [dbo].[utl_RemoveHousekeeping] @Table
	    IF @RC <> 0
	    BEGIN
	        SET @Message = 'Could not execute utl_RemoveHousekeeping for table ''' + @Table + '''.'
	        RAISERROR(@Message, 16, 1)
	        RETURN 4
	    END
	END
	
	-- get a list of columns in the primary key
	INSERT INTO @KeyColumns
	SELECT ColumnName
	FROM   dbo.fn_PrimaryKeyColumns(@Table)
	
	SELECT @ErrorCode = @@ERROR,
	       @RowsAffected = @@ROWCOUNT
	
	IF @RowsAffected = 0
	BEGIN
	    SET @Message = 'No primary key.  Housekeeping not added.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 5
	END
	
	-- disable triggers for table
	SET @SQL = 'ALTER TABLE ' + @Table + ' DISABLE TRIGGER ALL'
	EXECUTE sp_executesql @SQL
	
	SELECT @ErrorCode = @@ERROR,
	       @RowsAffected = @@ROWCOUNT
	
	IF @ErrorCode <> 0
	BEGIN
	    SET @Message = 'Could not disable triggers for table ''' + @Table + '''.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 6
	END
	
	--------------------------------------------------------------------------------
	--  process CreateDt                                                          --
	--------------------------------------------------------------------------------
	
	-- CreateDt: add column to the table
	IF dbo.fn_ColumnExists(@Table, 'CreateDt') = @False
	BEGIN
	    SET @SQL = 'ALTER TABLE ' + @Table + ' ADD CreateDt DATETIME NULL '
	    EXECUTE sp_executesql @SQL
	    
	    SELECT @ErrorCode = @@ERROR,
	           @RowsAffected = @@ROWCOUNT
	    
	    IF @ErrorCode <> 0
	    BEGIN
	        SET @Message = 'Could not alter table ''' + @Table + '''. Could not add column ''CreateDt''.'
	        RAISERROR(@Message, 16, 1)
	        RETURN 7
	    END
	END
	
	-- CreateDt: add default constraint
	IF dbo.fn_ColumnDefaultConstraint(@Table, 'CreateDt') IS NULL
	BEGIN
	    SET @SQL = 'ALTER TABLE ' + @Table + ' WITH NOCHECK ADD CONSTRAINT DF_' + @Table + '_CreateDt DEFAULT (GETDATE()) FOR CreateDt'
	    EXECUTE sp_executesql @SQL
	    
	    SELECT @ErrorCode = @@ERROR,
	           @RowsAffected = @@ROWCOUNT
	    
	    IF @ErrorCode <> 0
	    BEGIN
	        SET @Message = 'Could not alter table ''' + @Table + '''. Could not add default constraint for column ''CreateDt''.'
	        RAISERROR(@Message, 16, 1)
	        RETURN 8
	    END
	END
	
	-- CreateDt: update with default value where column is null
	SET @SQL = 'UPDATE ' + @Table + ' SET CreateDt = GETDATE() WHERE CreateDt IS NULL'
	EXECUTE sp_executesql @SQL
	
	SELECT @ErrorCode = @@ERROR,
	       @RowsAffected = @@ROWCOUNT
	
	IF @ErrorCode <> 0
	BEGIN
	    SET @Message = 'Could not update table ''' + @Table + ''', column ''CreateDt'' with getdate() where ''CreateDt'' is null.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 9
	END
	
	--  end of createdt  -----------------------------------------------------------
	
	
	--------------------------------------------------------------------------------
	--  process CreateUid                                                         --
	--------------------------------------------------------------------------------
	
	-- CreateUid: add column to the table
	IF dbo.fn_ColumnExists(@Table, 'CreateUid') = @False
	BEGIN
	    SET @SQL = 'ALTER TABLE ' + @Table + ' ADD CreateUid sysname NULL'
	    EXECUTE sp_executesql @SQL
	    SELECT @ErrorCode = @@ERROR,
	           @RowsAffected = @@ROWCOUNT
	    
	    IF @ErrorCode <> 0
	    BEGIN
	        SET @Message = 'Could not alter table ''' + @Table + '''. Could not add column ''CreateUid'''
	        RAISERROR(@Message, 16, 1)
	        RETURN 10
	    END
	END
	
	-- CreateUid: add default constraint
	IF dbo.fn_ColumnDefaultConstraint(@Table, 'CreateUid') IS NULL
	BEGIN
	    SET @SQL = 'ALTER TABLE ' + @Table + ' WITH NOCHECK ADD CONSTRAINT DF_' + @Table + '_CreateUid DEFAULT (SUSER_SNAME()) FOR CreateUid'
	    EXECUTE sp_executesql @SQL

	    SELECT @ErrorCode = @@ERROR,
	           @RowsAffected = @@ROWCOUNT
	    
	    IF @ErrorCode <> 0
	    BEGIN
	        SET @Message = 'Could not alter table ''' + @Table + '''. Could not add default constraint for column ''CreateUid''.'
	        RAISERROR(@Message, 16, 1)
	        RETURN 11
	    END
	END
	
	-- CreateUid: update with default value where column is null
	SET @SQL = 'UPDATE ' + @Table + ' SET CreateUid = SUSER_SNAME() WHERE CreateUid IS NULL'
	EXECUTE sp_executesql @SQL

	SELECT @ErrorCode = @@ERROR,
	       @RowsAffected = @@ROWCOUNT
	
	IF @ErrorCode <> 0
	BEGIN
	    SET @Message = 'Could not update table ''' + @Table + ''', column ''CreateUid'' with suser_sname() where ''CreateUid'' is null.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 12
	END
	
	--  end of CreateUid  ----------------------------------------------------------
	
	
	--------------------------------------------------------------------------------
	--  process RevMdlName                                                        --
	--------------------------------------------------------------------------------
	
	-- RevMdlName: add column to the table
	IF dbo.fn_ColumnExists(@Table, 'RevMdlName') = @False
	BEGIN
	    SET @SQL = 'ALTER TABLE ' + @Table + ' ADD RevMdlName sysname NULL'
	    EXECUTE sp_executesql @SQL

	    SELECT @ErrorCode = @@ERROR,
	           @RowsAffected = @@ROWCOUNT
	    
	    IF @ErrorCode <> 0
	    BEGIN
	        SET @Message = 'Could not alter table ''' + @Table + '''. Could not add column ''RevMdlName'''
	        RAISERROR(@Message, 16, 1)
	        RETURN 13
	    END
	END
	
	-- RevMdlName: this column has no default constraint
		
	SET @SQL = 'UPDATE ' + @Table + ' SET RevMdlName = ''' + @RevMdlName + ''' WHERE RevMdlName IS NULL'
	EXECUTE sp_executesql @SQL

	SELECT @ErrorCode = @@ERROR,
	       @RowsAffected = @@ROWCOUNT
	
	IF @ErrorCode <> 0
	BEGIN
	    SET @Message = 'Could not update table ''' + @Table + ''', column ''RevMdlName'' with ''' + @RevMdlName + ''' where ''RevMdlName'' is null.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 14
	END
	
	--  end of RevMdlName ----------------------------------------------------------
	
	
	--------------------------------------------------------------------------------
	--  process RevDt                                                             --
	--------------------------------------------------------------------------------
	
	-- RevDt: add column to the table
	IF dbo.fn_ColumnExists(@Table, 'RevDt') = @False
	BEGIN
	    SET @SQL = 'ALTER TABLE ' + @Table + ' ADD RevDt DATETIME NULL'
	    EXECUTE sp_executesql @SQL
	    
	    SELECT @ErrorCode = @@ERROR,
	           @RowsAffected = @@ROWCOUNT
	    
	    IF @ErrorCode <> 0
	    BEGIN
	        SET @Message = 'Could not alter table ''' + @Table + '''. Could not add column ''RevDt'''
	        RAISERROR(@Message, 16, 1)
	        RETURN 15
	    END
	END
	
	-- RevDt: add default constraint
	IF dbo.fn_ColumnDefaultConstraint(@Table, 'RevDt') IS NULL
	BEGIN
	    SET @SQL = 'ALTER TABLE ' + @Table + ' WITH NOCHECK ADD CONSTRAINT DF_' + @Table + '_RevDt DEFAULT (GETDATE()) FOR RevDt'
	    EXECUTE sp_executesql @SQL

	    SELECT @ErrorCode = @@ERROR,
	           @RowsAffected = @@ROWCOUNT

	    IF @ErrorCode <> 0
	    BEGIN
	        SET @Message = 'Could not alter table ''' + @Table + '''. Could not add default constraint for column ''RevDt''.'
	        RAISERROR(@Message, 16, 1)
	        RETURN 16
	    END
	END
	
	-- RevDt: update with default value where column is null
	SET @SQL = 'UPDATE ' + @Table + ' SET RevDt = GETDATE() WHERE RevDt IS NULL'
	EXECUTE sp_executesql @SQL

	SELECT @ErrorCode = @@ERROR,
	       @RowsAffected = @@ROWCOUNT
	
	IF @ErrorCode <> 0
	BEGIN
	    SET @Message = 'Could not update table ''' + @Table + ''', column ''RevDt'' with getdate() where ''RevDt'' is null.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 17
	END
	
	--  end of RevDt  --------------------------------------------------------------
	
	
	--------------------------------------------------------------------------------
	--  process RevUid                                                            --
	--------------------------------------------------------------------------------
	
	-- RevUid: add column to the table
	IF dbo.fn_ColumnExists(@Table, 'RevUid') = @False
	BEGIN
	    SET @SQL = 'ALTER TABLE ' + @Table + ' ADD RevUid sysname NULL'
	    EXECUTE sp_executesql @SQL

	    SELECT @ErrorCode = @@ERROR,
	           @RowsAffected = @@ROWCOUNT
	    
	    IF @ErrorCode <> 0
	    BEGIN
	        SET @Message = 'Could not alter table ''' + @Table + '''. Could not add column ''RevUid'''
	        RAISERROR(@Message, 16, 1)
	        RETURN 18
	    END
	END
	
	-- RevUid: add default constraint
	IF dbo.fn_ColumnDefaultConstraint(@Table, 'RevUid') IS NULL
	BEGIN
	    SET @SQL = 'ALTER TABLE ' + @Table + ' WITH NOCHECK ADD CONSTRAINT DF_' + @Table + '_RevUid DEFAULT (SUSER_SNAME()) FOR RevUid'
	    EXECUTE sp_executesql @SQL

	    SELECT @ErrorCode = @@ERROR,
	           @RowsAffected = @@ROWCOUNT
	    
	    IF @ErrorCode <> 0
	    BEGIN
	        SET @Message = 'Could not alter table ''' + @Table + '''. Could not add default constraint for column ''RevUid''.'
	        RAISERROR(@Message, 16, 1)
	        RETURN 19
	    END
	END
	
	-- RevUid: update with default value where column is null
	SET @SQL = 'UPDATE ' + @Table + ' SET RevUid = SUSER_SNAME() WHERE RevUid IS NULL'
	EXECUTE sp_executesql @SQL

	SELECT @ErrorCode = @@ERROR,
	       @RowsAffected = @@ROWCOUNT
	
	IF @ErrorCode <> 0
	BEGIN
	    SET @Message = 'Could not update table ''' + @Table + ''', column ''RevUid'' with suser_sname() where ''RevUid'' is null.'
	    RAISERROR(@Message, 16, 1)
	    RETURN 20
	END
	
	--  end of RevUid  -------------------------------------------------------------
	
	
	--------------------------------------------------------------------------------
	--  create trigger                                                            --
	--------------------------------------------------------------------------------
	
	SET @TriggerName = @Table + '_Housekeeping_InsUpdTrg'
	IF dbo.fn_TriggerExists(@Table, @TriggerName) = @False
	BEGIN
	    -- build from clause for use in creating the housekeeping trigger
	    SELECT @ID = MIN(ID)
	    FROM   @KeyColumns
	    
	    WHILE @ID IS NOT NULL
	    BEGIN
	        SELECT @ColumnName = ColumnName
	        FROM   @KeyColumns
	        WHERE  ID = @ID
	        
	        IF @FromClause IS NULL
	            SET @FromClause = 't.' + @ColumnName + ' = ' + 'i.' + @ColumnName
	        ELSE
	            SET @FromClause = @FromClause + ' AND ' + 't.' + @ColumnName + ' = ' + 'i.' + @ColumnName
	        
	        -- get next view to refresh
	        SELECT @ID = MIN(ID)
	        FROM   @KeyColumns
	        WHERE  ID > @ID
	    END
	    
	    SET @FromClause = 'FROM ' + @Table + ' t INNER JOIN inserted i ON ' + @FromClause
	    
	    -- build dynamic sql to create the housekeeping trigger
        SET @SQL = 'CREATE TRIGGER [' + @TriggerName + '] ON [dbo].' + @Table + ' ' + @CRLF
        SET @SQL = @SQL + 'FOR INSERT, UPDATE ' + @CRLF
        SET @SQL = @SQL + 'AS ' + @CRLF
        SET @SQL = @SQL + 'BEGIN ' + @CRLF
        SET @SQL = @SQL + @CRLF
        SET @SQL = @SQL + 'UPDATE ' + @Table + ' ' + @CRLF
        SET @SQL = @SQL + '        SET ' + @Table + '.RevDt = GETDATE(), ' + @CRLF
        SET @SQL = @SQL + '            ' + @Table + '.RevUid = SUSER_SNAME(), ' + @CRLF
        SET @SQL = @SQL + '            ' + @Table + '.RevMdlName = APP_NAME() ' + @CRLF
        SET @SQL = @SQL + '    ' + @FromClause + ' ' + @CRLF
        SET @SQL = @SQL + @CRLF
        SET @SQL = @SQL + 'END'
        
        EXECUTE sp_executesql @SQL

        SELECT @ErrorCode = @@ERROR,
               @RowsAffected = @@ROWCOUNT

        IF @ErrorCode <> 0
        BEGIN
            SET @Message = 'Could not create trigger ''' + @TriggerName + ''' on table ''' + @Table + '''.'
            RAISERROR(@Message, 16, 1)
            RETURN 19
        END
	END
	
	-- enable triggers for table
	SET @SQL = 'ALTER TABLE ' + @Table + ' ENABLE TRIGGER ALL'
	EXECUTE sp_executesql @SQL

	SELECT @ErrorCode = @@ERROR,
	       @RowsAffected = @@ROWCOUNT
	
	IF @ErrorCode <> 0
	BEGIN
	    SET @Message = 'Could not enable triggers for table ''' + @Table + '''.' 
	    RAISERROR(@Message, 16, 1)
	    RETURN 21
	END
	
	RETURN 0
END

